/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vzone.community;

import com.mysql.jdbc.Connection;
import java.lang.Object;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import vzone.sql.SqlHelper;
import vzone.structs.BasePeople;
import vzone.structs.Housing;
import vzone.structs.OperatorCode;

/**
 *
 * @author Kingpro
 */
public class Community implements ICommunity {

    private static final String IP = "localhost";
    private static final int PORT = 3306;
    private static final String DB = "community";
    private static final String USER = "root";
    private static final String PWD = "ogretzar";
    private static Community community = new Community();
    private static final String INSERT_PEOPLE = "INSERT INTO people (`idPeople`, `idAccount`, `idJob`, `name`, `nickName`, `gender`, `birthday`, `pwdHash`) " +
            "VALUES(@id1, @id2, @id3, @name, @nickName, @gender, @birthday, @pwdHash);";
    private static final String INSERT_JOB = "INSERT INTO job (`idJob`, `idCompany`, `jobName`, `startTimePerDay`, `endTimePerDay`, `jobDescription`, `pay`) " +
            "VALUES(@id1, @id2, @name, @startTime, @endTimePerDay, @description, @pay);";
    //登录时根据昵称查询数据库
    private static final String SELECT_PEOPLE = "SELECT * FROM  people WHERE `nickName`=@name";
    //根据人的idJob字段来查询人对应的工作信息
    private static final String SELECT_JOB = "SELECT * FROM job WHERE `idJob`=@jobId";
    private static final String UPDATE_PWD = "UPDATE people SET pwdHash=@pwd WHERE nickName=@name";
    //买房处理
    private static final String BUY_HOUSE = "UPDATE house SET idPeople=@id isSelling=@b WHERE idHouse=@id";
    //售房处理
    private static final String SELL_HOUSE = "UPDATE house SET isSelling=@b price=@p WHERE idHouse=@id";
    //CheckPeopleInfo中使用
    private static final String CHECK_PEOPLE = "SELECT * FROM  people WHERE `idPeople`=@id";

    public static Community getCommunity() {
        return community;
    }

    @Override
    public OperatorCode Register(BasePeople people, String pwdHashS) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        if (infoFull(people, pwdHashS)) {
            try {
                SqlHelper.ExecuteNonQuery(conn, INSERT_PEOPLE, new Object[]{
                            UUID.randomUUID(), people.accountIdUuid, people.JobJ.JobIDUuid, people.NameS, people.NickName, people.GenderB, people.BirthdayD, pwdHashS
                        });
                SqlHelper.ExecuteNonQuery(conn, INSERT_JOB, new Object[]{
                            people.JobJ.JobIDUuid
                        });
                return OperatorCode.SUCCESS;
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } else if (!accountExits(people)) {
            return OperatorCode.NOACCOUNT;//无此银行账户
        } else if (!pwdLegal(pwdHashS)) {
            return OperatorCode.INVALIEDPWD;
        }
        return OperatorCode.OTHER_ERROR;
    }

    @Override
    public OperatorCode Login(String NickNameS, String pwdHash, BasePeople people) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList resultAl = null;
        ArrayList resultJob = null;
        try {
            resultAl = SqlHelper.ExecuteReader(conn, SELECT_PEOPLE, new Object[]{NickNameS});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        Iterator<Object> resultI = resultAl.iterator();

        if (resultI.hasNext()) {
            //用户存在
            while (resultI.hasNext()) {
                Object[] resultOs = (Object[]) resultI.next();
                if (pwdHash == resultOs[7]) {
                    //此处相关类型转换需要确定such as "varchar" to "String"
                    people.PeopleIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[0]);
                    people.accountIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[1]);

                    people.NameS = (String) resultOs[3];//！
                    people.NickName = NickNameS;
                    people.GenderB = (Boolean) resultOs[5];

                    people.JobJ.JobIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[2]);

                    try {
                        resultJob = SqlHelper.ExecuteReader(conn, SELECT_JOB, new Object[]{people.JobJ.JobIDUuid});
                    } catch (Exception ex) {
                        Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    Iterator<Object> resultI2 = resultJob.iterator();
                    Object[] resultOs2 = (Object[]) resultI2.next();
                    people.JobJ.CompanyIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs2[1]);
                    people.JobJ.JobNameS = (String) resultOs2[3];
                    //其他job属性暂时不添加




                }
            }
        } else {
            //提示用户不存在
        }
        return OperatorCode.INVALIEDPWD;//密码错误

    }

    @Override
    public OperatorCode ChangePwd(UUID peopleIDUuid, String oldpwdHashS, String newpwdHash) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public OperatorCode ChangePwd(String NickNameS, String oldpwdHashS, String newpwdHash) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList resultAl = null;

        try {
            resultAl = SqlHelper.ExecuteReader(conn, SELECT_PEOPLE, new Object[]{NickNameS});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        Iterator<Object> resultI = resultAl.iterator();

        if (resultI.hasNext()) {
            //用户存在
            while (resultI.hasNext()) {
                Object[] resultOs = (Object[]) resultI.next();
                if (oldpwdHashS == resultOs[7]) {
                    //update
                    try {
                        SqlHelper.ExecuteNonQuery(conn, UPDATE_PWD, new Object[]{newpwdHash, NickNameS});
                    } catch (Exception ex) {
                        Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        } else {
            //提示用户不存在
        }
        return OperatorCode.INVALIEDPWD;//密码错误
    }

    @Override
    public boolean CheckPeopleInfo(UUID peopleID) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList resultAl = null;

        try {
            resultAl = SqlHelper.ExecuteReader(conn, CHECK_PEOPLE, new Object[]{peopleID});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (resultAl == null) {
            return false;
        } else {
            return true;
        }

    }

    @Override
    public BasePeople GetPeopleInfo(UUID peopleID) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList resultAl = null;
        ArrayList resultJob = null;
        BasePeople people = null;
        try {
            resultAl = SqlHelper.ExecuteReader(conn, CHECK_PEOPLE, new Object[]{peopleID});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        Iterator<Object> resultI = resultAl.iterator();
        if (resultI.hasNext()) {
            Object[] resultOs = (Object[]) resultI.next();
            people.PeopleIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[0]);
            people.accountIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[1]);
            people.JobJ.JobIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[2]);
            people.NameS = (String) resultOs[3];//！
            people.NickName = (String) resultOs[4];
            people.GenderB = (Boolean) resultOs[5];

            try {
                resultJob = SqlHelper.ExecuteReader(conn, SELECT_JOB, new Object[]{people.JobJ.JobIDUuid});
            } catch (Exception ex) {
                Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
            }
            Iterator<Object> resultI2 = resultJob.iterator();
            Object[] resultOs2 = (Object[]) resultI2.next();
            people.JobJ.CompanyIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs2[1]);
            people.JobJ.JobNameS = (String) resultOs2[3];
            //其他job属性暂时不添加
        }
        return people;
    }

    @Override
    public OperatorCode buyHouse(Housing house, BasePeople people) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);

        int resultI = 0;
        //需要根据账户Id执行"支付"
        //如成功处理在执行以下操作（住房信息的更新）
        try {
            resultI = SqlHelper.ExecuteNonQuery(conn, BUY_HOUSE, new Object[]{people.PeopleIdUuid, false, house.IdUuid});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (resultI != 0) {
            return OperatorCode.SUCCESS;
        } else {
            return OperatorCode.OTHER_ERROR;
        }

    }

    @Override
    public OperatorCode sellHouse(Housing house, int sellingPrice) {
        //修改自己持有的住房的出售状态，修改售价
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        int resultI = 0;
        //需要根据账户Id执行"支付"
        //如成功处理在执行以下操作（住房信息的更新）
        try {
            resultI = SqlHelper.ExecuteNonQuery(conn, SELL_HOUSE, new Object[]{false, sellingPrice, house.IdUuid});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (resultI != 0) {
            return OperatorCode.SUCCESS;
        } else {
            return OperatorCode.OTHER_ERROR;
        }
    }

    @Override
    public OperatorCode rentHouse(Housing house, int rentPrice, boolean perYear) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * 判断必要信息是否完整（注册时）
     * @param people
     * @param pwdHashS
     * @return
     */
    private boolean infoFull(BasePeople people, String pwdHashS) {
        if (people.NameS != null && people.accountIdUuid != null && people.NickName != null && pwdHashS != null) {
            return true;
        }
        return false;
    }

    @Override
    public OperatorCode charge(Charge charge) {
        
    }

    private boolean accountExits(BasePeople people) {
        //暂时不处理
        return true;
    }

    private boolean pwdLegal(String pwdHashS) {
        //暂时不处理
        return true;
    }
}
